package com.sean.dao;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class EmployeeDao extends HibernateDaoSupport implements IEmployeeDao {

	/**
	 * --coment: select employee whos roles are in (fake,admin) select * from
	 * employee as e where e.hr_id in (select distinct u.hr_id from user_role as
	 * u where u.role_name in ('fake','admin'))
	 */
	public List findEmployees(String[] roles) {
		if (roles == null) {
			//all
			return getHibernateTemplate().find("from Employee as e where e.id in (select distinct u.adminemployee.id from UserRole u)");
		} else if(roles.length == 1 && roles[0].equals("none")){
			//none
			return getHibernateTemplate().find("from Employee as e where e.id not in (select distinct u.adminemployee.id from UserRole u )");
		}else {
			//common
			String hql = "from Employee as e where e.id in (select distinct u.adminemployee.id from UserRole as u where u.userrolepk.rolename in (:roles))";
			Query hQuery = getSession().createQuery(hql);
			hQuery.setParameterList("roles", roles, Hibernate.STRING);
			List eList = hQuery.list();

			return eList;
		}
	}

	/**
	 * A Way can work --coment: select employee whos roles are in (fake,admin)
	 * select * from employee as e where e.hr_id in (select distinct u.hr_id
	 * from user_role as u where u.role_name in ('fake','admin'))
	 */
	public List findEmployeesNewWay(String[] roles) {
		if (roles == null) {
			return getHibernateTemplate().find("from Employee as e");
		} else {
			// 1: Find the hr_id list
			String hr_idListSql = "select distinct u.hr_id from user_role as u where u.role_name in ('fake','admin')";
			SQLQuery sqlQuery = getSession().createSQLQuery(hr_idListSql);
			List list = sqlQuery.list();
			logger.debug("Hr_ID list-> " + list);

			// 2: Find the employees
			String hql = "from Employee as e where e.id in (:ids)";
			Query hQuery = getSession().createQuery(hql);
			hQuery.setParameterList("ids", list, Hibernate.BIG_INTEGER);
			List eList = hQuery.list();

			return eList;
		}
	}
}
